/*
 Navicat Premium Data Transfer

 Source Server         : sbject
 Source Server Type    : PostgreSQL
 Source Server Version : 140003
 Source Host           : localhost:5432
 Source Catalog        : sbject
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 140003
 File Encoding         : 65001

 Date: 18/06/2022 23:56:26
*/


-- ----------------------------
-- Sequence structure for cargo_companies_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."cargo_companies_id_seq";
CREATE SEQUENCE "public"."cargo_companies_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for cargos_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."cargos_id_seq";
CREATE SEQUENCE "public"."cargos_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for incars_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."incars_id_seq";
CREATE SEQUENCE "public"."incars_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for outcars_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."outcars_id_seq";
CREATE SEQUENCE "public"."outcars_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Sequence structure for port_companies_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."port_companies_id_seq";
CREATE SEQUENCE "public"."port_companies_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 2147483647
START 1
CACHE 1;

-- ----------------------------
-- Table structure for cargo_companies
-- ----------------------------
DROP TABLE IF EXISTS "public"."cargo_companies";
CREATE TABLE "public"."cargo_companies" (
  "id" int4 NOT NULL DEFAULT nextval('cargo_companies_id_seq'::regclass),
  "name" text COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of cargo_companies
-- ----------------------------
INSERT INTO "public"."cargo_companies" VALUES (1, '哈哈哈哈');
INSERT INTO "public"."cargo_companies" VALUES (2, '小黄美容美发公司');
INSERT INTO "public"."cargo_companies" VALUES (3, '胖胖水泥');

-- ----------------------------
-- Table structure for cargos
-- ----------------------------
DROP TABLE IF EXISTS "public"."cargos";
CREATE TABLE "public"."cargos" (
  "id" int4 NOT NULL DEFAULT nextval('cargos_id_seq'::regclass),
  "cargo_company_id" int4,
  "cargo_company_name" text COLLATE "pg_catalog"."default",
  "port_company_id" text COLLATE "pg_catalog"."default",
  "port_company_name" text COLLATE "pg_catalog"."default",
  "in_date" date,
  "car_id" text COLLATE "pg_catalog"."default",
  "in_count" int4,
  "in_weight" numeric,
  "sailing_day" date,
  "order_id" text COLLATE "pg_catalog"."default",
  "out_count" int4,
  "out_weight" numeric,
  "extraction_id" text COLLATE "pg_catalog"."default",
  "business_id" text COLLATE "pg_catalog"."default",
  "storage_fee" numeric,
  "remaining_weight" numeric
)
;

-- ----------------------------
-- Records of cargos
-- ----------------------------
INSERT INTO "public"."cargos" VALUES (19, 0, '', '', '', '2021-04-18', '黑H12345', 700, 200, '2021-05-16', 'PO#123456', 720, 37.6, '177UB123456', 'LLH12345', 282, 502.4);

-- ----------------------------
-- Table structure for incars
-- ----------------------------
DROP TABLE IF EXISTS "public"."incars";
CREATE TABLE "public"."incars" (
  "id" int4 NOT NULL DEFAULT nextval('incars_id_seq'::regclass),
  "in_date" date,
  "car_id" text COLLATE "pg_catalog"."default",
  "in_count" int4,
  "in_weight" numeric,
  "order_id" text COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of incars
-- ----------------------------
INSERT INTO "public"."incars" VALUES (5, '2021-04-17', '黑H1234', 800, 220, '123466');
INSERT INTO "public"."incars" VALUES (3, '2021-04-17', '黑H1234', 600, 120, 'PO#123456');
INSERT INTO "public"."incars" VALUES (2, '2021-04-18', '黑H12345', 700, 200, 'PO#123456');

-- ----------------------------
-- Table structure for outcars
-- ----------------------------
DROP TABLE IF EXISTS "public"."outcars";
CREATE TABLE "public"."outcars" (
  "id" int4 NOT NULL DEFAULT nextval('outcars_id_seq'::regclass),
  "sailing_day" date,
  "out_count" int4,
  "out_weight" numeric,
  "extraction_id" text COLLATE "pg_catalog"."default",
  "business_id" text COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Records of outcars
-- ----------------------------
INSERT INTO "public"."outcars" VALUES (2, '2021-05-16', 720, 37.6, '177UB123456', 'LLH12345');

-- ----------------------------
-- Table structure for port_companies
-- ----------------------------
DROP TABLE IF EXISTS "public"."port_companies";
CREATE TABLE "public"."port_companies" (
  "id" int4 NOT NULL DEFAULT nextval('port_companies_id_seq'::regclass),
  "name" text COLLATE "pg_catalog"."default",
  "area" numeric
)
;

-- ----------------------------
-- Records of port_companies
-- ----------------------------
INSERT INTO "public"."port_companies" VALUES (1, '琪琪美容有限公司', 600);
INSERT INTO "public"."port_companies" VALUES (2, '胖胖火药公司', 1000);

-- ----------------------------
-- Function structure for count_port
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."count_port"();
CREATE OR REPLACE FUNCTION "public"."count_port"()
  RETURNS "pg_catalog"."int4" AS $BODY$

declare
counts integer;

begin select count(*) into counts from port_companies;
return counts;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for delete_id
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."delete_id"();
CREATE OR REPLACE FUNCTION "public"."delete_id"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
begin
	delete from cargos where id = old.id;
	return old;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."cargo_companies_id_seq"
OWNED BY "public"."cargo_companies"."id";
SELECT setval('"public"."cargo_companies_id_seq"', 3, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."cargos_id_seq"
OWNED BY "public"."cargos"."id";
SELECT setval('"public"."cargos_id_seq"', 19, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."incars_id_seq"
OWNED BY "public"."incars"."id";
SELECT setval('"public"."incars_id_seq"', 5, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."outcars_id_seq"
OWNED BY "public"."outcars"."id";
SELECT setval('"public"."outcars_id_seq"', 2, true);

-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
ALTER SEQUENCE "public"."port_companies_id_seq"
OWNED BY "public"."port_companies"."id";
SELECT setval('"public"."port_companies_id_seq"', 2, true);

-- ----------------------------
-- Primary Key structure for table cargo_companies
-- ----------------------------
ALTER TABLE "public"."cargo_companies" ADD CONSTRAINT "cargo_companies_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table cargos
-- ----------------------------
ALTER TABLE "public"."cargos" ADD CONSTRAINT "cargos_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table incars
-- ----------------------------
ALTER TABLE "public"."incars" ADD CONSTRAINT "incars_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table outcars
-- ----------------------------
ALTER TABLE "public"."outcars" ADD CONSTRAINT "outcars_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Primary Key structure for table port_companies
-- ----------------------------
ALTER TABLE "public"."port_companies" ADD CONSTRAINT "port_companies_pkey" PRIMARY KEY ("id");
